package iq.poi;

/**   
 * 需要的jar包：
 xmlbeans-2.3.0.jar
 poi-scratchpad-3.6-20091214.jar
 poi-ooxml-3.6-20091214.jar
 poi-3.6-20091214.jar  
 poi-ooxml-schemas-3.6-20091214.jar 
 */

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;

import org.apache.poi.POIXMLDocument;
import org.apache.poi.POIXMLTextExtractor;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hwpf.extractor.WordExtractor;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xwpf.extractor.XWPFWordExtractor;
import org.apache.xmlbeans.XmlException;

public class Test {
	public static void main(String[] args) {
		try {
			String excelText = Test.extractTextFromXLS("C:\\Documents and Settings\\Administrator\\桌面\\test.xlsx");
			writeXSL();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * @Method: extractTextFromDOCX
	 * @Description: 从word 2003文档中提取纯文本
	 * 
	 * @param
	 * @return String
	 * @throws
	 */
	public static String extractTextFromDOC(InputStream is) throws IOException {
		WordExtractor ex = new WordExtractor(is); // is是WORD文件的InputStream

		return ex.getText();
	}

	/**
	 * @Method: extractTextFromDOCX
	 * @Description: 从word 2007文档中提取纯文本
	 * 
	 * @param
	 * @return String
	 * @throws
	 */
	public static String extractTextFromDOC2007(String fileName)
			throws IOException, OpenXML4JException, XmlException {
		OPCPackage opcPackage = POIXMLDocument.openPackage(fileName);
		POIXMLTextExtractor ex = new XWPFWordExtractor(opcPackage);

		return ex.getText();
	}


	/**
	 * @Description: 从excel文档中读数据
	 * 碰到过的异常org.apache.poi.POIXMLException: java.lang.reflect.InvocationTargetException
	 * 在main中运行时没有问题,但放到tomcat项目中发生异常,poi-ooxml-schemas-3.7-20101029.jar替换成ooxml-schemas-1.1.jar可以解决;
	 * 但问题应该是因为使用jrebel3.0插件,去掉或换用3.6.1解决
	 * @param
	 * @return String
	 * @throws
	 */
	private static String extractTextFromXLS(String fileName)
			throws Exception {
		StringBuffer content = new StringBuffer();

		// 构造 XSSFWorkbook 对象，strPath 传入文件路径
		Workbook wb = (Workbook) WorkbookFactory.create(new FileInputStream(fileName));

		// 循环工作表Sheet
		for (int numSheet = 0; numSheet < wb.getNumberOfSheets(); numSheet++) {
			Sheet xSheet = wb.getSheetAt(numSheet);
			if (xSheet == null) {
				continue;
			}

			// 循环行Row 
			for (int rowNum = 0; rowNum <= xSheet.getLastRowNum(); rowNum++) {
				Row xRow = xSheet.getRow(rowNum);
				if (xRow == null) {
					continue;
				}

				// 循环列Cell
				for (int cellNum = 0; cellNum <= xRow.getPhysicalNumberOfCells(); cellNum++) {
					System.out.println("xRow.getLastCellNum() = " + xRow.getPhysicalNumberOfCells());
					Cell cell = xRow.getCell(cellNum);//Row.CREATE_NULL_AS_BLANK?
					if (cell == null) {
						continue;
					}
					
					Object obj_content = "";
					if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
						obj_content = "";
					} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
						obj_content = cell.getBooleanCellValue();
					} else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
						if(DateUtil.isCellDateFormatted(cell)){  
							obj_content = cell.getDateCellValue();  
		                  }else{   
		                    cell.setCellType(Cell.CELL_TYPE_STRING);  
		                    String temp = cell.getStringCellValue();  
		                    //判断是否包含小数点，如果不含小数点，则以字符串读取，如果含小数点，则转换为Double类型的字符串  
		                    if(temp.indexOf(".")>-1){  
		                    	obj_content = String.valueOf(new Double(temp)).trim();  
		                    }else{  
		                    	obj_content = temp.trim();  
		                    }  
		                  }
					} else {
						obj_content = cell.getStringCellValue();
					}
					System.out.println(obj_content);
				}
			}
		}

		return content.toString();
	}

	/**
	 * @param args
	 * @throws Exception 
	 */
	public static void writeXSL() throws Exception {
		POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("src/poi/workbook.xls"));
		Workbook wb = new HSSFWorkbook(fs);
		//HSSFSheet sheet1 = wb.createSheet("第一张表");
		//HSSFSheet sheet2 = wb.createSheet();
		//wb.setSheetName(1, "第二张表");
		
		Row row = wb.getSheetAt(0).getRow(0);
		if (row==null) {
			row = wb.getSheetAt(0).createRow(0);
		}
		Cell cell = row.getCell(0);
		if (cell == null) {
			cell = row.createCell(0);
		}
		cell.setCellValue("测试字符串2");
		
		row = wb.getSheetAt(1).getRow(1);
		if (row==null) {
			row = wb.getSheetAt(1).createRow(1);
		}
		cell = row.getCell(1);
		if (cell == null) {
			cell = row.createCell(1);
		}
		cell.setCellValue("第二个测试字符串2");
		FileOutputStream fileOut = new FileOutputStream("src/poi/workbook.xls");
		wb.write(fileOut);
		fileOut.close();
	}
}
